#mssql数据同步至sqlite
import sqlite3
import collections
import pymssql
##################### MSSSQL 数据库信息 ####################
server='localhost'
user='sa'
password='123456'
dbname='myTest'
#################### Sqlite 数据库信息 #####################
sqlpath='downdata.db'
#################### Sqlite 数据库 表 #####################
baseTable=[]
baseTable.append({'tableName':'OkDetail','sql':'create table OkDetail(Id integer not null primary key,ParentId integer not null,Remark nvarchar(500) null,MingChen nvarchar(500) null,BieMing nvarchar(1000) null,DaoYan nvarchar(500) null,ZhuYan nvarchar(1000) null,LeiXing nvarchar(100) null,DiQu nvarchar(200) null,YuYan nvarchar(100) null,ShangYing nvarchar(500) null,PianChang nvarchar(100) null,GengXing nvarchar(100) null,Juqing nvarchar(4000) null,PingFen float null,ZongPin int null default(0),PinFen int null default(0),ImgUrl nvarchar(500) null,ImgAlt nvarchar(500) null)'})
baseTable.append({'tableName':'OkDown','sql':'create table OkDown(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,DownUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'OkMu38','sql':'create table OkMu38(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,PlayUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'OkPlay','sql':'create table OkPlay(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,PlayUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'OkSource','sql':'create table OkSource(Id integer  primary key not null,ReqUrl varchar(100) not null,VideoName varchar(200) not null,VideoType varchar(50) not null,TypeCode integer not null,UpdateTime DATE not null,PullTime DATETIME not null,IsEnd BIT not null  default(0),IsDeal BIT not null default(0))'})
baseTable.append({'tableName':'ZuiDaDetail','sql':'create table ZuiDaDetail(Id integer not null primary key,ParentId integer not null,Remark nvarchar(500) null,MingChen nvarchar(500) null,BieMing nvarchar(1000) null,DaoYan nvarchar(500) null,ZhuYan nvarchar(1000) null,LeiXing nvarchar(100) null,DiQu nvarchar(200) null,YuYan nvarchar(100) null,ShangYing nvarchar(500) null,PianChang nvarchar(100) null,GengXing nvarchar(100) null,Juqing nvarchar(4000) null,PingFen float null,ZongPin int null default(0),PinFen int null default(0),ImgUrl nvarchar(500) null,ImgAlt nvarchar(500) null)'})
baseTable.append({'tableName':'ZuiDaDown','sql':'create table ZuiDaDown(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,DownUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'ZuiDaMu38','sql':'create table ZuiDaMu38(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,PlayUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'ZuiDaPlay','sql':'create table ZuiDaPlay(Id integer primary key not null,ParentId integer not null,Title nvarchar(200) not null,PlayUrl nvarchar(1000) not null)'})
baseTable.append({'tableName':'ZuiDaSource','sql':'create table ZuiDaSource(Id integer  primary key not null,ReqUrl varchar(100) not null,VideoName varchar(200) not null,VideoType varchar(50) not null,TypeCode integer not null,UpdateTime DATE not null,PullTime DATETIME not null,IsEnd BIT not null  default(0),IsDeal BIT not null default(0))'})
# 检查数据库是否存在表
def CheackSqliteTable(tbname,sqls):
    conn=sqlite3.connect(sqlpath)
    cursor=conn.cursor() #获取光标
    try:
        #该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。
        cursor.execute('select name,tbl_name from sqlite_master where type=? and name=?',('table',tbname)) #执行SQL        
        rows=cursor.fetchall() #获取所有数据
        if len(rows)==0:
            cursor.execute(sqls)
            conn.commit()
        else:
            # 清空已存在的数据 防止报错
            cursor.execute(' delete from '+tbname+' where 1=1 ')
            conn.commit()
    except AttributeError as e:
        print(e)   #输出异常
    finally:
        conn.close()  # 数据库操作完成后要关闭连接
# 写入数据
def WriteToSqlite(strsql:str,params:list):
    if len(params)==0:
        return
    conn=sqlite3.connect(sqlpath)
    cursor=conn.cursor() #获取光标
    try:
        cursor.executemany(strsql,params)
        conn.commit()
    except AttributeError as e:
        print(e)   #输出异常
    finally:
        conn.close()  # 数据库操作完成后要关闭连接
# 读取MSSQL数据库数据
def ReadMssqlData(sqls:str):
    conn=pymssql.connect(server,user,password,dbname,charset='utf8') #连接数据库 (服务器地址,登录数据库账号,登录数据库账号密码,指定数据库)
    cursor=conn.cursor() #获取光标
    try:
        cursor.execute(sqls) #执行SQL        
        rows=cursor.fetchall() #获取所有数据
        conn.commit()
        return rows
    except AttributeError as e:
        print(e)   #输出异常
        return []
    finally:
        conn.close()  # 数据库操作完成后要关闭连接
# 写入数据
def DealPlayUrl():
    zsql='select * from OkPlay '
    zlist=ReadMssqlData(zsql)
    isql='insert into OkPlay(Id,ParentId,Title,PlayUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    zsql='select * from OkMu38 '
    zlist=ReadMssqlData(zsql)
    isql='insert into OkMu38(Id,ParentId,Title,PlayUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    zsql='select * from ZuiDaPlay '
    zlist=ReadMssqlData(zsql)
    isql='insert into ZuiDaPlay(Id,ParentId,Title,PlayUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    zsql='select * from ZuiDaMu38 '
    zlist=ReadMssqlData(zsql)
    isql='insert into ZuiDaMu38(Id,ParentId,Title,PlayUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    
def DealDownUrl():
    zsql='select * from OkDown '
    zlist=ReadMssqlData(zsql)
    isql='insert into OkDown(Id,ParentId,Title,DownUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    zsql='select * from ZuiDaDown '
    zlist=ReadMssqlData(zsql)
    isql='insert into ZuiDaDown(Id,ParentId,Title,DownUrl)values(?,?,?,?) '
    WriteToSqlite(isql,zlist)
    
def DealDetail():
    zsql='select * from OkDetail '
    zlist=ReadMssqlData(zsql)
    isql='insert into OkDetail(Id,ParentId,Remark,MingChen,BieMing,DaoYan,ZhuYan,LeiXing,DiQu,YuYan,ShangYing,PianChang,GengXing,Juqing,PingFen,ZongPin,PinFen,ImgUrl,ImgAlt)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) '
    WriteToSqlite(isql,zlist)
    zsql='select * from ZuiDaDetail '
    zlist=ReadMssqlData(zsql)
    isql='insert into ZuiDaDetail(Id,ParentId,Remark,MingChen,BieMing,DaoYan,ZhuYan,LeiXing,DiQu,YuYan,ShangYing,PianChang,GengXing,Juqing,PingFen,ZongPin,PinFen,ImgUrl,ImgAlt)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) '
    WriteToSqlite(isql,zlist)
        
def DealSource():
    zsql='select * from OkSource '
    zlist=ReadMssqlData(zsql)
    isql='insert into OkSource(Id,ReqUrl,VideoName,VideoType,TypeCode,UpdateTime,PullTime,IsEnd,IsDeal)values(?,?,?,?,?,?,?,?,?)'
    WriteToSqlite(isql,zlist)
    zsql='select * from ZuiDaSource '
    zlist=ReadMssqlData(zsql)
    isql='insert into ZuiDaSource(Id,ReqUrl,VideoName,VideoType,TypeCode,UpdateTime,PullTime,IsEnd,IsDeal)values(?,?,?,?,?,?,?,?,?)'
    WriteToSqlite(isql,zlist)
   
def DealSqliteTable():
    for t in baseTable:
        CheackSqliteTable(t['tableName'],t['sql'])
        # print(t['tableName'])
        # print(t['sql'])
# 检查数据库表是否存在
DealSqliteTable()
# 导入数据
# 播放链接
DealPlayUrl()
# 下载链接
DealDownUrl()
# 详情
DealDetail()
# 采集链接
DealSource()